﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private DataTable dt_rep;
        private delegate void Deleg();
        private Deleg del;
        private DataTable dt_pharm = new DataTable();
        private DataTable dt_pharm_sel = new DataTable();
        private struct Pharmflag { public int code; public bool fl; public int baseid; public int cityid; }
        private Pharmflag[] phfl = new Pharmflag[] { };
        private DateTime dateBal;
        
        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private Pharmflag[] PharmFlUpd(Pharmflag[] pfl, DataTable dt, string codename)
        {
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < pfl.Length; i++)
                    if (pfl[i].code == Convert.ToInt32(dr[codename]) && pfl[i].baseid == Convert.ToInt32(dr["ИД базы"]) && pfl[i].cityid == Convert.ToInt32(dr["ИД города"]))
                        pfl[i].fl = Convert.ToBoolean(dr[namechcol]);

            }
            return pfl;
        }

        private DataTable DownloadData(SqlConnection con, DataTable dt, string proc)
        {
            SqlCommand pharm = con.CreateCommand();
            pharm.CommandText = proc;
            pharm.CommandType = CommandType.StoredProcedure;
            dt = new DataTable();
            dt.Load(pharm.ExecuteReader());
            dt.Columns[0].ReadOnly = false;
            return dt;
        }
        
        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            dateTimePicker1.Value = DateTime.Now.AddDays(-1).Date;
            dateBal = DateTime.Now.Date.AddDays(-1);
            label1.Visible = false;
            progressBar1.Visible = false;
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dt_rep.Clear();
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                
                dt_rep.Load(RepProc(conn));

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }

        }

        private SqlDataReader RepProc(SqlConnection sqlcon)//метод для вызова хранимки
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"sp_name"; //имя хранимой процедуры
            sqlcom.CommandTimeout = 300;

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //- Скорректировать остатки
            int rez = 0;
            conn.Open();
            this.Text += " Данные загружаются";
            progressBar1.Step = 1;
            progressBar1.Minimum = 0;
            progressBar1.Maximum = dt_pharm_sel.Rows.Count;
            progressBar1.Value = 0;
            for (int i = 0; i < dt_pharm_sel.Rows.Count; i++)
            {
                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[4];
                par[0].name = "@dateins";
                par[0].value = dateBal;
                par[1].name = "@baseid";
                par[1].value = null;
                par[2].name = "@phcode";
                par[2].value = Convert.ToInt32(dt_pharm_sel.Rows[i][0].ToString());
                par[3].name = "@daysback";
                par[3].value = 1;
                rez = (new SqlStorProc()).SqlProcExecNonQuery(conn, "pas_FillDrugBalance", 300, par);
                progressBar1.PerformStep();
                if (rez == 100)
                {
                    MessageBox.Show("Не загружено");
                }
                
                
            }
            conn.Close();
            this.Text.Replace(" Данные загружаются", "");
            MessageBox.Show("Данные загружены");
            button2.Visible = false;
            label1.Visible = false;
            progressBar1.Visible = false;
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //- Фильтр по аптекам
            dt_pharm_sel = null;
            dt_pharm_sel = new DataTable();
            DataTable dt_view = new DataTable();
            dt_view = dt_pharm.Clone();
            listBox1.Items.Clear();
            label1.Visible = false;
            progressBar1.Visible = false;
            PharmFilter.PharmFilter p = new PharmFilter.PharmFilter();
            conn.Open();
            dt_pharm = DownloadData(conn, dt_pharm, "pas_PharmSel");
            conn.Close();
            p.dataGridView1.DataSource = dt_pharm.DefaultView;
            dt_pharm_sel.Columns.Add("Код аптеки");
            p.namechcol = namechcol;
            p.Text = "Фильтр по аптекам";
            p.sortcoumn = "Город";
            p.StartPosition = FormStartPosition.CenterScreen;
            DialogResult res = p.ShowDialog();
            p.Close();
            phfl = PharmFlUpd(phfl, dt_pharm, "Код аптеки");
            if (res == DialogResult.OK)
            {
                foreach (DataRow row in dt_pharm.Rows)
                {
                    if (Convert.ToBoolean(row[namechcol]))
                    {
                        listBox1.Items.Add(row["Аптека"].ToString());
                        dt_pharm_sel.Rows.Add(row[1]);
                    }
                }

                button2.Visible = true;
                label1.Visible = true;
                progressBar1.Visible = true;
            }
            else
            {
                button2.Visible = false;
                label1.Visible = false;
                progressBar1.Visible = false;
            }
         //   dataGridView1.DataSource = dt_pharm_sel.DefaultView;
        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            //- Смена дня загрузки остатков
            dateBal = dateTimePicker1.Value.Date;
        }
    }
}
